﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace Service_Gallery
{
    // REMARQUE : vous pouvez utiliser la commande Renommer du menu Refactoriser pour changer le nom de classe "Service" à la fois dans le code, le fichier svc et le fichier de configuration.
    public class Service : IService
    {
        private string executeNonQuery(string query)
        {
            SqlConnection oConnection = new SqlConnection(Constant.CONNECTION_STR);

            string strReturn = null;
            try
            {
                oConnection.Open();
                SqlCommand oCommand = new SqlCommand(query, oConnection);
                strReturn = ((Int32)oCommand.ExecuteScalar()).ToString();
            }
            catch (Exception e)
            {
                Console.WriteLine("Erreur :" + e.Message);
            }

            return strReturn;
        }

        private SqlDataReader executeReader(string query)
        {
            SqlConnection oConnection = new SqlConnection(Constant.CONNECTION_STR);
            SqlDataReader sqlDataReaderReturn = null;
            try
            {
                oConnection.Open();
                SqlCommand oCommand = new SqlCommand(query, oConnection);
                sqlDataReaderReturn = oCommand.ExecuteReader();
                oConnection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Erreur :" + e.Message);
            }
            return sqlDataReaderReturn;
        }

        public string addUser(string name, string login, string password)
        {
            return executeNonQuery("INSERT INTO [User] ([name], [login], [password], [type]) "
                            + "OUTPUT INSERTED.ID VALUES ('" + name + "', '" + login + "', '" + password + "', 0)");
        }

        public bool deleteUser(string userId)
        {
            executeNonQuery("DELETE FROM [User] WHERE [id] = '" + userId + "'");
            return true;
        }

        public List<string> getUserList()
        {
            string queryStr = "SELECT [id], [name], [login], [type] FROM [User]";

            SqlConnection oConnection = new SqlConnection(Constant.CONNECTION_STR);
            SqlDataReader oReader = null;
            List<string> userList = new List<string>();
            try
            {
                oConnection.Open();
                SqlCommand oCommand = new SqlCommand(queryStr, oConnection);
                oReader = oCommand.ExecuteReader();

                userList.Add(oReader.GetName(0) + " " + oReader.GetName(1) + " " + oReader.GetName(2) + " " + oReader.GetName(3));
                while (oReader.Read())
                {
                    userList.Add(oReader.GetInt32(0) + " " + oReader.GetString(1) + " " + oReader.GetString(2) + " " + oReader.GetInt32(3));
                }
                oConnection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Erreur :" + e.Message);
            }
            finally
            {
                oReader.Close();
            }
            return userList;
        }

        public string getUserInfo(string userId)
        {
            string queryStr = "SELECT [id], [name], [login], [type] FROM [gallery].[dbo].[User] WHERE [id] = '" + userId + "'";

            SqlConnection oConnection = new SqlConnection(Constant.CONNECTION_STR);
            SqlDataReader oReader = null;
            string user = null;
            try
            {
                oConnection.Open();
                SqlCommand oCommand = new SqlCommand(queryStr, oConnection);
                oReader = oCommand.ExecuteReader();
                while (oReader.Read())
                {
                    user = oReader.GetInt32(0) + " " + oReader.GetString(1) + " " + oReader.GetString(2) + " " + oReader.GetInt32(3);
                }
                oConnection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Erreur :" + e.Message);
            }
            finally
            {
                oReader.Close();
            }
            return user;
        }

        public string connectUser(string login, string password)
        {
            return executeNonQuery("SELECT [id] FROM [User] WHERE [login] = '" + login + "'"
                 + " AND [password] = '" + password + "'");
        }

        public bool allowUserAlbum(string albumId, string userId, int permission)
        {
            executeNonQuery("INSERT INTO [User_Album] ([albumId], [userId], [permission]) "
               + "VALUES ('" + albumId + "', '" + userId + "', '" + permission + "')");
            return true;
        }

        public bool disallowUserAlbum(string albumId, string userId)
        {
            executeNonQuery("DELETE FROM [User_Album] WHERE [albumID] = '" + albumId
               + "' AND [userId] = '" + userId + "'");
            return true;
        }

        public List<string> getUserAlbum(string userId)
        {
            string queryStr = "SELECT [id], [name], [date], [permission] FROM [Album] RIGHT JOIN [User_Album] ON [id] = [albumId] WHERE [userId] = '" + userId + "'";

            SqlConnection oConnection = new SqlConnection(Constant.CONNECTION_STR);
            SqlDataReader oReader = null;
            List<string> userAlbum = new List<string>();
            try
            {
                oConnection.Open();
                SqlCommand oCommand = new SqlCommand(queryStr, oConnection);
                oReader = oCommand.ExecuteReader();

                userAlbum.Add(oReader.GetName(0) + " " + oReader.GetName(1) + " " + oReader.GetName(2) + " " + oReader.GetName(3));
                while (oReader.Read())
                {
                    userAlbum.Add(oReader.GetInt32(0) + " " + oReader.GetString(1) + " " + oReader.GetDateTime(2) + " " + oReader.GetInt32(3));
                }
                oConnection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Erreur :" + e.Message);
            }
            finally
            {
                oReader.Close();
            }
            return userAlbum;
        }

        public List<string> getViewableAlbum(string userId, int permission)
        {
            string queryStr = "SELECT [id], [name], [date], [permission] FROM [Album] RIGHT JOIN [User_Album] ON [id] = [albumId] WHERE [userId] = '" + userId + "' AND [permission] = '" + permission + "'";

            SqlConnection oConnection = new SqlConnection(Constant.CONNECTION_STR);
            SqlDataReader oReader = null;
            List<string> userAlbum = new List<string>();
            try
            {
                oConnection.Open();
                SqlCommand oCommand = new SqlCommand(queryStr, oConnection);
                oReader = oCommand.ExecuteReader();

                userAlbum.Add(oReader.GetName(0) + " " + oReader.GetName(1) + " " + oReader.GetName(2) + " " + oReader.GetName(3));
                while (oReader.Read())
                {
                    userAlbum.Add(oReader.GetInt32(0) + " " + oReader.GetString(1) + " " + oReader.GetDateTime(2) + " " + oReader.GetInt32(3));
                }
                oConnection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Erreur :" + e.Message);
            }
            finally
            {
                oReader.Close();
            }
            return userAlbum;
        }

        public string addAlbum(string name, string userId)
        {
            String albumId = executeNonQuery("INSERT INTO [Album] ([name]) "
                         + "OUTPUT INSERTED.ID VALUES ('" + name + "')");

            executeNonQuery("INSERT INTO [User_Album] ([userId], [albumId], [permission]) "
                + "VALUES ('" + userId + "', '" + albumId + "', '" + Constant.OWNER + "')");

            return albumId;

        }

        public bool deleteAlbum(string albumId)
        {
            executeNonQuery("DELETE FROM [Album] WHERE [id] = '" + albumId + "'");
            return true;
        }

        public List<string> getAlbumList()
        {
            string queryStr = "SELECT [id], [name], [date] FROM [Album]";

            SqlConnection oConnection = new SqlConnection(Constant.CONNECTION_STR);
            SqlDataReader oReader = null;
            List<string> albumList = new List<string>();
            try
            {
                oConnection.Open();
                SqlCommand oCommand = new SqlCommand(queryStr, oConnection);
                oReader = oCommand.ExecuteReader();

                albumList.Add(oReader.GetName(0) + " " + oReader.GetName(1) + " " + oReader.GetName(2));
                while (oReader.Read())
                {
                    albumList.Add(oReader.GetInt32(0) + " " + oReader.GetString(1) + " " + oReader.GetDateTime(2));
                }
                oConnection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Erreur :" + e.Message);
            }
            finally
            {
                oReader.Close();
            }

            return albumList;
        }

        public string getAlbumInfo(string albumId)
        {
            string queryStr = "SELECT [id], [name], [date] FROM [Album] WHERE [id] = '" + albumId + "'";

            SqlConnection oConnection = new SqlConnection(Constant.CONNECTION_STR);
            SqlDataReader oReader = null;
            string album = null;
            try
            {
                oConnection.Open();
                SqlCommand oCommand = new SqlCommand(queryStr, oConnection);
                oReader = oCommand.ExecuteReader();
                while (oReader.Read())
                {
                    album = oReader.GetInt32(0) + " " + oReader.GetString(1) + " " + oReader.GetDateTime(2);
                }

                oConnection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Erreur :" + e.Message);
            }
            finally
            {
                oReader.Close();
            }
            return album;
        }

        public List<string> getAlbumPicture(string albumId)
        {
            string queryStr = "SELECT [id], [name], [date] FROM [Picture] WHERE [albumId] = " + albumId;

            SqlConnection oConnection = new SqlConnection(Constant.CONNECTION_STR);
            SqlDataReader oReader = null;
            List<string> albumPictures = new List<string>();
            try
            {
                oConnection.Open();
                SqlCommand oCommand = new SqlCommand(queryStr, oConnection);
                oReader = oCommand.ExecuteReader();
                albumPictures.Add(oReader.GetName(0) + " " + oReader.GetName(1) + " " + oReader.GetName(2));
                while (oReader.Read())
                {
                    albumPictures.Add(oReader.GetInt32(0) + " " + oReader.GetString(1) + " " + oReader.GetDateTime(2));
                }

                oReader.Close();
                oConnection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Erreur :" + e.Message);
            }
            return albumPictures;
        }

        public string addPicture(string albumId, string name, byte[] picture)
        {
            SqlConnection oConnection = new SqlConnection(Constant.CONNECTION_STR);
            string strReturn = null;
            try
            {
                oConnection.Open();
                SqlCommand oCommand = new SqlCommand("INSERT INTO [Picture] ([albumId], [name], [blob], [size]) " +
                    "OUTPUT INSERTED.ID VALUES ('" + albumId + "', '" + name + "', @Blob, '" + picture.Length + "')", oConnection);
                oCommand.Parameters.Add("@Blob", SqlDbType.Image, picture.Length).Value = picture;
                strReturn = ((Int32)oCommand.ExecuteScalar()).ToString();
                oConnection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Erreur :" + e.Message);
            }
            return strReturn;
        }

        public bool deletePicture(string pictureId)
        {
            executeNonQuery("DELETE FROM [Picture] WHERE [id] = '" + pictureId + "'");
            return true;
        }

        public byte[] getPicture(string pictureId)
        {
            string query = "SELECT [id], [size], [blob], [name] FROM [Picture] WHERE [id] = " + pictureId;
            byte[] blob = null;
            SqlConnection oConnection = new SqlConnection(Constant.CONNECTION_STR);
            try
            {
                oConnection.Open();
                SqlCommand oCommand = new SqlCommand(query, oConnection);
                SqlDataReader myReader = oCommand.ExecuteReader(CommandBehavior.SequentialAccess);
                if (myReader.Read())
                {
                    // lit la taille du blob
                    int size = myReader.GetInt32(1);
                    blob = new byte[size];
                    // récupére le blob de la BDD et le copie dans la variable blob
                    myReader.GetBytes(2, 0, blob, 0, size);
                }
                oConnection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Erreur :" + e.Message);
            }
            return blob;
        }

        public List<string> getPictureList()
        {
            string queryStr = "SELECT [id], [name], [date], [albumId] FROM [Picture]";

            SqlConnection oConnection = new SqlConnection(Constant.CONNECTION_STR);
            SqlDataReader oReader = null;
            List<string> picture = new List<string>();
            try
            {
                oConnection.Open();
                SqlCommand oCommand = new SqlCommand(queryStr, oConnection);
                oReader = oCommand.ExecuteReader();

                picture.Add(oReader.GetName(0) + " " + oReader.GetName(1) + " " + oReader.GetName(2) + " " + oReader.GetName(3));
                while (oReader.Read())
                {
                    picture.Add(oReader.GetInt32(0) + " " + oReader.GetString(1) + " " + oReader.GetDateTime(2) + " " + oReader.GetInt32(3));
                }
                oConnection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Erreur :" + e.Message);
            }
            finally
            {
                oReader.Close();
            }
            return picture;
        }

        private static string logFile = @"d:\ConsoleOutput.txt";

        public string getPictureInfo(string pictureId)
        {
            string queryStr = "SELECT [id], [name], [date] FROM [Picture] WHERE [id] = '" + pictureId + "'";

            FileStream fileStream = new FileStream(logFile, FileMode.Append, FileAccess.Write);
            BinaryWriter bw = new BinaryWriter(fileStream);
            bw.Write("queryStr :" + queryStr + "\n");



            SqlConnection oConnection = new SqlConnection(Constant.CONNECTION_STR);
            SqlDataReader oReader = null;
            string picture = null;
            try
            {
                oConnection.Open();
                SqlCommand oCommand = new SqlCommand(queryStr, oConnection);
                oReader = oCommand.ExecuteReader();

                while (oReader.Read())
                {
                    picture = oReader.GetInt32(0) + " " + oReader.GetString(1) + " " + oReader.GetDateTime(2);
                }
                oConnection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Erreur :" + e.Message);
            }
            finally
            {
                oReader.Close();
            }

            bw.Close();
            fileStream.Close();
            return picture;
        }
    }
}
